package Util;

import android.content.Context;
import android.os.Looper;
import android.util.Log;
import android.view.Gravity;
import android.widget.Toast;

import net.sourceforge.jtds.jdbc.DateTime;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;

import entity.OperateMessage;
import entity.StorageMessage;
import entity.StorageMessageEx;

public class DBUtil {

    private static String IP = "192.168.0.38";
    private static String DBName = "TestConnection";
    private static String USER = "sa";
    private static String PWD = "Sa921102";
    private static Connection conn=null;
    private static Statement stmt=null;
    private static ResultSet rs=null;
    private static PreparedStatement pstmt = null ;
    private static SimpleDateFormat simpleDateFormat;
    /** 创建数据库对象 */


    private static Connection getSQLConnection() {
        Connection con = null;
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            //加上 useunicode=true;characterEncoding=UTF-8 防止中文乱码
            con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + IP + ":1433/" + DBName + ";useunicode=true;characterEncoding=UTF-8", USER, PWD);
//            Looper.prepare();
//            Toast.makeText(context,"连接成功"+con,Toast.LENGTH_SHORT).show();
//            Looper.loop();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();

        } catch (SQLException e) {
            e.printStackTrace();

        }
        return con;
    }
    /** 关闭连接*/
    public static void closeAll(){
        try{
            if (rs!=null){
                rs.close();
            }
            if (stmt!=null){
                stmt.close();
            }
            if (conn!=null){
                conn.close();
            }
            System.out.println("连接正常关闭");
        }catch (Exception e){
            System.out.println(e);
        }

    }

    public  static  List QueryWhereDate(String startDate ,String endDate){
        List users=new ArrayList<>();

        try {
            conn = getSQLConnection();
            if(conn!=null)
                Log.d("sqlserver", "数据库连接成功");
            String sql = "select * from dbo.StorageMessage where storageDate>=? and storageDate<=?";
            pstmt = conn.prepareStatement(sql);//
            pstmt.setString(1,startDate);
            pstmt.setString(2,endDate);
            rs = pstmt.executeQuery();



            while (rs.next()) {
                Integer id=rs.getInt("id");
                String code=rs.getString("code");
                Integer qualifiedNumber = rs.getInt("qualifiedNumber");
                Integer unQualifiedNumber=rs.getInt("unQualifiedNumber");
                String inOut=rs.getString("inOut");
                String storage_man=rs.getString("storage_man");
                Date storageDate=rs.getDate("storageDate");
                Date createTime=rs.getTimestamp("createTime");
                Integer creatorId=rs.getInt("creatorId");
                Date updateTime=rs.getTimestamp("updateTime");

                Integer updaterId=rs.getInt("updaterId");
                StorageMessageEx storageMsg=new StorageMessageEx(id,code,qualifiedNumber,unQualifiedNumber,inOut,storage_man,storageDate,createTime,creatorId,updateTime,updaterId,null);
                users.add(storageMsg);
            }

        } catch (SQLException e) {
            e.printStackTrace();

        }finally {
            closeAll();
        }
        return users;
    }

    /** 查询数据库 */
    public static List QueryStorage() {
        List users=new ArrayList<>();

        try {
             conn = getSQLConnection();
             if(conn!=null)
                Log.d("sqlserver", "数据库连接成功");
             String sql = "select * from dbo.StorageMessage";
             stmt = conn.createStatement();//
             rs = stmt.executeQuery(sql);


            while (rs.next()) {
                Integer id=rs.getInt("id");
                String code=rs.getString("code");
                Integer qualifiedNumber = rs.getInt("qualifiedNumber");
                Integer unQualifiedNumber=rs.getInt("unQualifiedNumber");
                String inOut=rs.getString("inOut");
                String storage_man=rs.getString("storage_man");
                Date storageDate=rs.getDate("storageDate");
                Date createTime=rs.getTimestamp("createTime");
                Integer creatorId=rs.getInt("creatorId");
                Date updateTime=rs.getTimestamp("updateTime");

                Integer updaterId=rs.getInt("updaterId");
                StorageMessageEx storageMsg=new StorageMessageEx(id,code,qualifiedNumber,unQualifiedNumber,inOut,storage_man,storageDate,createTime,creatorId,updateTime,updaterId,null);
                users.add(storageMsg);
            }

        } catch (SQLException e) {
            e.printStackTrace();

        }finally {
            closeAll();
        }
        return users;
    }

    /** 查询数据库 */
    public static List QueryOperator() {
        List users=new ArrayList<>();
        int i=0;
        try {
            conn = getSQLConnection();
            if(conn!=null)
                Log.d("sqlserver", "数据库连接成功");
            String sql = "select * from dbo.OperateMessage";
            stmt = conn.createStatement();//

            rs = stmt.executeQuery(sql);


            while (rs.next()) {
                String operatorName=rs.getString("operatorName");
                Integer operateTimes = rs.getInt("operateTimes");
                Integer storageId=rs.getInt("storageId");
                OperateMessage operateMessage=new OperateMessage();
                operateMessage.setOperatorName(operatorName);
                operateMessage.setStorageId(storageId);
                operateMessage.setOperateTimes(operateTimes);
                users.add(operateMessage);
            }

        } catch (SQLException e) {
            e.printStackTrace();

        }finally {
            closeAll();
        }
        return users;
    }


    public static  int  insert(StorageMessage storageMsg){
        String sql = "INSERT INTO dbo.StorageMessage(code,qualifiedNumber,unQualifiedNumber,inOut,storage_man,storageDate) VALUES(?,?,?,?,?,?)" ;
        conn =getSQLConnection();
        int i=0;


        if(conn!=null)
            Log.d("sqlserver", "数据库连接成功");
        try {
            pstmt = conn.prepareStatement(sql) ;
            pstmt.setString(1,storageMsg.getCode());
            pstmt.setInt(2,storageMsg.getQualifiedNumber());
            pstmt.setInt(3,storageMsg.getUnQualifiedNumber());
            pstmt.setString(4,storageMsg.getInOut());
            pstmt.setString(5,storageMsg.getStorage_man());
            pstmt.setDate(6,new java.sql.Date(new Date().getTime()));

            i=pstmt.executeUpdate();


        } catch (Exception e1){
            System.out.println(e1);

        } finally{
            closeAll();
        }
        return  i;
    }

    public static  int  insertOM(List<OperateMessage> list){
        PreparedStatement pst;
        ResultSet rs=null;

        String sql = "INSERT INTO dbo.OperateMessage(operatorName,operateTimes,storageId) VALUES(?,?,?)" ;
        conn =getSQLConnection();
        if(conn!=null)
            Log.d("sqlserver", "数据库连接成功,开始向OperateMessage添加数据");
        int i=0;
        for (int j = 0; j <list.size() ; j++) {

            try{
                if (list.get(j).getOperateTimes()!=0){
                    pst=conn.prepareStatement(sql);
                    pst.setString(1,list.get(j).getOperatorName());
                    pst.setInt(2,list.get(j).getOperateTimes());
                    pst.setInt(3,list.get(j).getStorageId());
                    i=pst.executeUpdate();

                }
            }catch (Exception e){
                Log.e("Exception", e+"" );

            }finally {

            }
        }

        closeAll();


        return  i;
    }

    public static int delete(Integer id){
        String sql = "DELETE FROM dbo.StorageMessage WHERE ID=?" ;
        conn =getSQLConnection();
        int i=0;

        if(conn!=null)
            Log.d("sqlserver", "数据库连接成功");
        try {
            pstmt = conn.prepareStatement(sql) ;
            pstmt.setInt(1,id);
//            pstmt.setString(2,"d");
            i=pstmt.executeUpdate();
            Log.d("加载驱动", "更新成功");

        } catch (Exception e1){
            System.out.println(e1);
        } finally{
            closeAll();
        }
        return  i;
    }

    public static int update(){
        String sql = "UPDATE dbo.StorageMessage SET name=? where id=?" ;
        conn =getSQLConnection();
        int i=0;

        if(conn!=null)
            Log.d("sqlserver", "数据库连接成功");
        try {
            pstmt = conn.prepareStatement(sql) ;
            pstmt.setString(1,"d");
            pstmt.setInt(2,3);
//            pstmt.setString(2,"d");
            i=pstmt.executeUpdate();
            Log.d("加载驱动", "更新成功");

        } catch (Exception e1){
            System.out.println(e1);
        } finally{
            closeAll();
        }
        return  i;
    }


    public static Integer queryIdByCode(String c){
        Integer id=0;
        try {
            conn = getSQLConnection();

            if(conn!=null)
                Log.d("sqlserver", "数据库连接成功");
            String sql = "select id from dbo.StorageMessage where code=?";
            pstmt = conn.prepareStatement(sql) ;
            pstmt.setString(1,c);
            rs=pstmt.executeQuery();

            while (rs.next()) {
               id=rs.getInt("id");

            }

        } catch (SQLException e) {
            e.printStackTrace();

        }finally {
            closeAll();
        }
        return id;
    }

    //获得当前时间(精确到秒)
    public static Date getdateEx() {
        simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.CHINA);
        simpleDateFormat.setTimeZone(TimeZone.getTimeZone("GMT+08"));
        String s=simpleDateFormat.format(new Date());
        Date d=null;
        try{
            simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.CHINA);
            d=simpleDateFormat.parse(s);
        }catch (ParseException e){
            System.out.println(e.getLocalizedMessage());
        }

        return d;
    }

}
